<html>
<head>
<META http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>Chapter&nbsp;8.&nbsp;SQL-Invoked Routines</title>
<link href="../docbook.css" rel="stylesheet" type="text/css">
<meta content="DocBook XSL-NS Stylesheets V1.74.0" name="generator">
<meta name="keywords" content="Hsqldb, HyperSQL, SQL">
<meta name="keywords" content="Hsqldb, HyperSQL, Database, JDBC, Java">
<link rel="home" href="index.html" title="HyperSQL User Guide">
<link rel="up" href="index.html" title="HyperSQL User Guide">
<link rel="prev" href="ch07.html" title="Chapter&nbsp;7.&nbsp;Data Access and Change">
<link rel="next" href="triggers-chapt.html" title="Chapter&nbsp;9.&nbsp;Triggers">
</head>
<body bgcolor="white" text="black" link="#0000FF" vlink="#840084" alink="#0000FF">
<div class="navheader">
<table summary="Navigation header" width="100%">
<tr>
<th align="center" colspan="3">Chapter&nbsp;8.&nbsp;SQL-Invoked Routines</th>
</tr>
<tr>
<td align="left" width="20%"><a accesskey="p" href="ch07.html"><img src="../images/db/prev.gif" alt="Prev"></a>&nbsp;</td><th align="center" width="60%">&nbsp;</th><td align="right" width="20%">&nbsp;<a accesskey="n" href="triggers-chapt.html"><img src="../images/db/next.gif" alt="Next"></a></td>
</tr>
</table>
<hr>
</div>
<div class="chapter" lang="en">
<div class="titlepage">
<div>
<div>
<h2 class="title">
<a name="sqlroutines-chapt"></a>Chapter&nbsp;8.&nbsp;SQL-Invoked Routines</h2>
</div>
<div>
<div class="authorgroup">
<div class="author">
<h3 class="author">
<span class="firstname">Fred</span> <span class="surname">Toussi</span>
</h3>
<div class="affiliation">
<span class="orgname">The HSQL Development Group<br>
</span>
</div>
</div>
</div>
</div>
<div>
<p class="releaseinfo">$Revision: 3318 $</p>
</div>
<div>
<div class="legalnotice">
<a name="N1223F"></a>
<p>Copyright 2009 Fred Toussi. Permission is granted to distribute
      this document without any alteration under the terms of the HSQLDB
      license. Additional permission is granted to the HSQL Development Group
      to distribute this document with or without alterations under the terms
      of the HSQLDB license.</p>
</div>
</div>
<div>
<p class="pubdate">$Date: 2009-12-06 18:44:53 -0500 (Sun, 06 Dec 2009) $</p>
</div>
</div>
</div>
<div class="toc">
<p>
<b>Table of Contents</b>
</p>
<dl>
<dt>
<span class="section"><a href="sqlroutines-chapt.html#N12274">SQL Language Routines (PSM)</a></span>
</dt>
<dd>
<dl>
<dt>
<span class="section"><a href="sqlroutines-chapt.html#N122AE">Compound Statement</a></span>
</dt>
<dt>
<span class="section"><a href="sqlroutines-chapt.html#N122C0">Variables</a></span>
</dt>
<dt>
<span class="section"><a href="sqlroutines-chapt.html#N122D2">Handlers</a></span>
</dt>
<dt>
<span class="section"><a href="sqlroutines-chapt.html#N12300">Assignment Statement</a></span>
</dt>
<dt>
<span class="section"><a href="sqlroutines-chapt.html#N12316">Select Statement : Single Row</a></span>
</dt>
<dt>
<span class="section"><a href="sqlroutines-chapt.html#N12331">Formal Parameters</a></span>
</dt>
<dt>
<span class="section"><a href="sqlroutines-chapt.html#N1234C">Iterated Statements</a></span>
</dt>
<dt>
<span class="section"><a href="sqlroutines-chapt.html#N12363">Conditional Statements</a></span>
</dt>
<dt>
<span class="section"><a href="sqlroutines-chapt.html#N123AC">Return Statement</a></span>
</dt>
<dt>
<span class="section"><a href="sqlroutines-chapt.html#N123C7">Control Statements</a></span>
</dt>
<dt>
<span class="section"><a href="sqlroutines-chapt.html#N123E9">General SQL Statements</a></span>
</dt>
<dt>
<span class="section"><a href="sqlroutines-chapt.html#N123FD">Routine Overloading</a></span>
</dt>
</dl>
</dd>
<dt>
<span class="section"><a href="sqlroutines-chapt.html#N1240C">Java Language Routines (SQL/JRT)</a></span>
</dt>
<dt>
<span class="section"><a href="sqlroutines-chapt.html#N1249D">Routine Definition</a></span>
</dt>
</dl>
</div>
<p>SQL-invoked routines are functions and procedures called from SQL.
  HyperSQL 2.0 supports routines conforming to two parts of the SQL Standard.
  Routines written in the SQL language are supported in conformance to SQL/PSM
  (Persistent Stored Modules) specification. Routines written in Java are
  supported in conformance to SQL/JRT specification. In addition, HyperSQL&rsquo;s
  previous non-standard support for calling Java routines without prior method
  definition is retained and enhanced in the latest version by extending the
  SQL/JRT specification.</p>
<p>SQL-invoked routines are schema objects. Naming and referencing
  follows conventions common to all schema objects. The same routine name can
  be defined in two different schemas and used with schema-qualified
  references.</p>
<p>A routine is either a procedure or a function.</p>
<p>A function:</p>
<div class="itemizedlist">
<ul type="disc">
<li>
<p>is defined with CREATE FUNCTION</p>
</li>
<li>
<p>always returns a value</p>
</li>
<li>
<p>does not modify the data in the database</p>
</li>
<li>
<p>is called as part of an SQL statement</p>
</li>
<li>
<p>can have parameters</p>
</li>
<li>
<p>can be overloaded</p>
</li>
</ul>
</div>
<p>A procedure:</p>
<div class="itemizedlist">
<ul type="disc">
<li>
<p>is defined with CREATE PROCEDURE</p>
</li>
<li>
<p>can return one or more values</p>
</li>
<li>
<p>can modify the data in the database</p>
</li>
<li>
<p>is called separately, using the CALL statement</p>
</li>
<li>
<p>can have parameter</p>
</li>
<li>
<p>can be overloaded</p>
</li>
</ul>
</div>
<p>Definition of routine signature and characteristics, name resolution
  and invocation are all implemented uniformly for routines written in SQL or
  Java.</p>
<div class="section" lang="en">
<div class="titlepage">
<div>
<div>
<h2 class="title" style="clear: both">
<a name="N12274"></a>SQL Language Routines (PSM)</h2>
</div>
</div>
</div>
<p>The PSM (Persistent Stored Module) specification extends the SQL
    language to allow definition of both SQL Function and SQL procedure bodies
    with the same structure and the same control statements (such as
    conditional and loop statements) with minor exceptions.</p>
<p>The routine body is a SQL statement. In its simplest form, the body
    is a single SQL statement. A simple example of a function is given
    below:</p>
<pre class="programlisting">CREATE FUNCTION an_hour_before (t TIMESTAMP)
RETURNS TIMESTAMP
RETURN t - 1 HOUR
</pre>
<p>An example of the use of the function in an SQL statement is given
    below:</p>
<pre class="programlisting">SELECT an_hour_before(event_timestamp) AS notification_timestamp, event_name FROM events;</pre>
<p>A simple example of a procedure is given below:</p>
<pre class="programlisting">CREATE PROCEDURE new_customer(firstname VARCHAR(50), lastname VARCHAR(50))
MODIFIES SQL DATA
INSERT INTO CUSTOMERS VALUES (DEFAULT, firstname, lastname, CURRENT_TIMESTAMP)
</pre>
<p>The procedure inserts a row into an existing table with the
    definition given below:</p>
<pre class="programlisting">CREATE TABLE customers(id INTEGER GENERATED BY DEFAULT AS IDENTITY, firstname VARCHAR(50), lastname VARCHAR(50), added TIMESTAMP);</pre>
<p>An example of the use of the procedure is given below:</p>
<pre class="programlisting">CALL new_customer('JOHN', 'SMITH');</pre>
<p>The routine body is often a compound statement. A compound statement
    can contain one or more SQL statements, which can include control
    statements, as well as nested compound statements.</p>
<p>ROUTINE STATEMENTS</p>
<p>The following SQL Statements can be used only in routines.</p>
<p>
<code class="literal">&lt;assignment statement&gt;</code>
</p>
<p>
<code class="literal">&lt;compound statement&gt;</code>
</p>
<p>
<code class="literal">&lt;case statement&gt;</code>
</p>
<p>
<code class="literal">&lt;if statement&gt;</code>
</p>
<p>
<code class="literal">&lt;while statement&gt;</code>
</p>
<p>
<code class="literal">&lt;repeat statement&gt;</code>
</p>
<p>
<code class="literal">&lt;for statement&gt;</code>
</p>
<p>
<code class="literal">&lt;loop statement&gt;</code>
</p>
<p>
<code class="literal">&lt;signal statement&gt;</code>
</p>
<div class="section" lang="en">
<div class="titlepage">
<div>
<div>
<h3 class="title">
<a name="N122AE"></a>Compound Statement</h3>
</div>
</div>
</div>
<p>A compound statement is enclosed in a BEGIN / END block with
      optional labels. It can contain one or more <code class="literal">&lt;SQL variable
      declaration&gt;</code> or <code class="literal">&lt;handler
      declaration&gt;</code> before at least one SQL statement. The BNF is
      given below:</p>
<p>
<code class="literal">&lt;compound statement&gt; ::= [ &lt;beginning
      label&gt; &lt;colon&gt; ] BEGIN [[NOT] ATOMIC] [{&lt;SQL variable
      declaration&gt; &lt;semicolon&gt;} ...] [{&lt;handler declaration&gt;
      &lt;semicolon&gt;}...] {&lt;SQL procedure statement&gt;
      &lt;semicolon&gt;} ... END [ &lt;ending label&gt; ]</code>
</p>
<p>An example of a simple compound statement body is given below. It
      performs the common task of inserting related data into two table. The
      IDENTITY value that is automatically inserted in the first table is
      retrieved using the IDENTITY() function and inserted into the second
      table.</p>
<pre class="programlisting">CREATE PROCEDURE new_customer(firstname VARCHAR(50), lastname  VARCHAR(50), address VARCHAR(100))
    MODIFIES SQL DATA
    BEGIN ATOMIC
    INSERT INTO customers VALUES (DEFAULT, firstname, lastname, CURRENT_TIMESTAMP);
    INSERT INTO addresses VALUES (DEFAULT, IDENTITY(), address);
    END</pre>
</div>
<div class="section" lang="en">
<div class="titlepage">
<div>
<div>
<h3 class="title">
<a name="N122C0"></a>Variables</h3>
</div>
</div>
</div>
<p>A <code class="literal">&lt;variable declaration&gt;</code> defines the name
      and data type of the variable and, optionally, its default value. In the
      next example, a variable is used to hold the IDENTITY value. In
      addition, the formal parameters of the procedure are identified as input
      parameters with the use of the optional IN keyword. This procedure does
      exactly the same job as the other procedure.</p>
<pre class="programlisting">CREATE PROCEDURE new_customer(IN firstname VARCHAR(50), IN lastname VARCHAR(50), IN address VARCHAR(100))
    MODIFIES SQL DATA
    BEGIN ATOMIC
    DECLARE temp_id INTEGER;
    INSERT INTO CUSTOMERS VALUES (DEFAULT, firstname, lastname, CURRENT_TIMESTAMP);
    SET temp_id = IDENTITY();
    INSERT INTO ADDRESSES VALUES (DEFAULT, temp_id, address);
    END</pre>
<p>The BNF for variable declaration is given below:</p>
<p>
<code class="literal">&lt;SQL variable declaration&gt; ::= DECLARE
      &lt;variable name list&gt; &lt;data type&gt; [DEFAULT &lt;default
      value&gt;]</code>
</p>
<p>
<code class="literal">&lt;variable name list&gt; ::= &lt;variable name&gt; [
      { &lt;comma&gt; &lt;variable name&gt; }... ]</code>
</p>
</div>
<div class="section" lang="en">
<div class="titlepage">
<div>
<div>
<h3 class="title">
<a name="N122D2"></a>Handlers</h3>
</div>
</div>
</div>
<p>A <code class="literal">&lt;handler declaration&gt;</code> defines the
      course of action when an exception or warning is raised during the
      execution of the compound statement. A compound statement may have one
      or more handler declarations. These handlers become active when code
      execution enters the compound statement block and remain active in any
      sub-block and statement within the block. The handlers become inactive
      when code execution leaves the block.</p>
<p>In the previous example, if an exception is thrown during the
      execution of either SQL statement, the execution of the compound
      statement is terminated and the exception is propagated and thrown by
      the CALL statement for the procedure. A handler declaration can resolve
      the thrown exception within the compound statement without propagating
      it, and allow the execution of the &lt;compound statement&gt; to
      continue.</p>
<p>In the example below, the UNDO handler declaration catches any
      exception that is thrown during the execution of the compound statement
      inside the BEGIN / END block. As it is an UNDO handler, all the changes
      to data performed within the compound statement (BEGIN / END) block are
      rolled back. The procedure then returns without throwing an
      exception.</p>
<pre class="programlisting">CREATE PROCEDURE NEW_CUSTOMER(IN firstname VARCHAR(50), IN lastname VARCHAR(50), IN address VARCHAR(100))
    MODIFIES SQL DATA
    label_one: BEGIN ATOMIC
    DECLARE temp_id INTEGER;
    DECLARE UNDO HANDLER FOR SQLEXCEPTION LEAVE label_one;
    INSERT INTO CUSTOMERS VALUES (DEFAULT, firstname, lastname, CURRENT_TIMESTAMP);
    SET temp_id = IDENTITY();
    INSERT INTO ADDRESSES VALUES (DEFAULT, temp_id, address);
    END</pre>
<p>Other types of hander are CONTINUE and EXIT handlers. A CONTINUE
      handler ignores any exception and proceeds to the next statement in the
      block. An EXIT handler terminates execution without undoing the data
      changes performed by the previous (successful) statements.</p>
<p>The conditions can be general conditions, or specific conditions.
      Among general conditions that can be specified, SQLEXCEPTION covers all
      exceptions, SQLWARNING covers all warnings, while NOT FOUND covers the
      not-found condition, which is raised when a DELETE, UPDATE, INSERT or
      MERGE statement completes without actually affecting any row.
      Alternatively, one or more specific conditions can be specified
      (separated with commas) which apply to specific exceptions or warnings
      or classes or exceptions or warnings. A specific condition is specified
      with <code class="literal">SQLSTATE &lt;value&gt;</code>, for example SQLSTATE
      'W_01003' specifies the warning raised after a SQL statement is executed
      which contains an aggregate function which encounters a null value
      during execution. An example is given below which activates the handler
      when either of the two warnings is raised:</p>
<pre class="programlisting">DECLARE UNDO HANDLER FOR SQLSTATE 'W_01003', 'W_01004' LEAVE label_one;</pre>
<p>The BNF for <code class="literal">&lt;handler declaration&gt;</code> is
      given below:</p>
<p>
<code class="literal">&lt;handler declaration&gt; ::= DECLARE {UNDO |
      CONTINUE | EXIT} HANDLER FOR {SQLEXCEPTION | SQLWARNING | NOT FOUND} | {
      SQL_STATE &lt;state value&gt; [, ...]} [&lt;SQL procedure
      statement&gt;];</code>
</p>
<p>A handler declaration may specify an SQL procedure statement to be
      performed when the handler is activated. When an exception occurs, the
      example below performs the UNDO as in the previous example, then inserts
      the (invalid) data into a separate table.</p>
<pre class="programlisting">DECLARE UNDO HANDLER FOR SQLEXCEPTION
    INSERT INTO invalid_customers VALUES(firstanme, lastname, address);</pre>
<p>The <code class="literal">&lt;SQL procedure statement&gt;</code> is required
      by the SQL Standard but is optional in HyperSQL. If the execution of the
      <code class="literal">&lt;SQL procedure statement&gt;</code> specified in the
      handler declaration throws an exception itself, then it is handled by
      the handlers that are currently active. The <code class="literal">&lt;SQL procedure
      statement&gt;</code> can itself be a compound statement with its own
      handlers.</p>
</div>
<div class="section" lang="en">
<div class="titlepage">
<div>
<div>
<h3 class="title">
<a name="N12300"></a>Assignment Statement</h3>
</div>
</div>
</div>
<p>The SET statement is used for assignment. It can be used flexibly
      with rows or single values. The BNF is given below:</p>
<p>
<code class="literal">&lt;assignment statement&gt; ::= &lt;singleton
      variable assignment&gt; | &lt;multiple variable
      assignment&gt;</code>
</p>
<p>
<code class="literal">&lt;singleton variable assignment&gt; ::= SET
      &lt;assignment target&gt; &lt;equals operator&gt; &lt;assignment
      source&gt;</code>
</p>
<p>
<code class="literal">&lt;multiple variable assignment&gt; ::= SET
      (&lt;variable or parameter&gt;, ...) = &lt;row value
      expression&gt;</code>
</p>
<p>In the example below, the result of the SELECT is assigned to two
      OUT or INOUT &nbsp;parameters. The SELECT must return one row. If it returns
      more than one, an exception is raised. If it returns no row, no change
      is made to ARG1 and ARG2.</p>
<pre class="programlisting">SET (arg1, arg2) = (SELECT col1, col2 FROM atable WHERE id = 10);</pre>
<p>In the example below, the result of a function call is assigned to
      VAR1.</p>
<pre class="programlisting">SET var1 = SQRT(var2);</pre>
</div>
<div class="section" lang="en">
<div class="titlepage">
<div>
<div>
<h3 class="title">
<a name="N12316"></a>Select Statement : Single Row</h3>
</div>
</div>
</div>
<p>A special form of SELECT can also be used for assigning values
      from a query to one or more parameters or variables. This works similar
      to a SET statement that has a SELECT statement as the source.</p>
<a name="N1231B" class="indexterm"></a>
<p>
<span class="bold"><strong>SELECT : SINGLE ROW</strong></span>
</p>
<p>
<span class="emphasis"><em>select statement: single row</em></span>
</p>
<p>
<code class="literal">&lt;select statement: single row&gt; ::= SELECT [
      &lt;set quantifier&gt; ] &lt;select list&gt; INTO &lt;select target
      list&gt; &lt;table expression&gt;</code>
</p>
<p>
<code class="literal">&lt;select target list&gt; ::= &lt;target
      specification&gt; [ { &lt;comma&gt; &lt;target specification&gt; }...
      ]</code>
</p>
<p>Retrieve values from a specified row of a table assigns the
      fields to the specified targets. The example below has an identical
      effect to the example of SET statement given above.</p>
<pre class="programlisting">SELECT col1, col2 INTO arg1, arg2 FROM atable WHERE id = 10;</pre>
</div>
<div class="section" lang="en">
<div class="titlepage">
<div>
<div>
<h3 class="title">
<a name="N12331"></a>Formal Parameters</h3>
</div>
</div>
</div>
<p>Each parameter of a procedure can be defined as IN, OUT or INOUT.
      An IN parameter is an input to the procedure and is passed by value. The
      value cannot be modified inside the procedure body. An OUT parameter is
      a reference for output. An INOUT parameter is a reference for both input
      and output. An OUT or INOUT parameter is passed by reference, therefore
      only a dynamic parameter argument or a variable within an enclosing
      procedure can be passed for it. The assignment statement is used to
      assign a value to an OUT or INOUT parameter.</p>
<p>In the example below, the procedure is declared with an OUT
      parameter.</p>
<pre class="programlisting">CREATE PROCEDURE new_customer(OUT newid INT, IN firstname VARCHAR(50), IN lastname VARCHAR(50), IN address VARCHAR(100))
    MODIFIES SQL DATA
    BEGIN ATOMIC
    DECLARE temp_id INTEGER;
    INSERT INTO CUSTOMERS VALUES (DEFAULT, firstname, lastname, CURRENT_TIMESTAMP);
    SET temp_id = IDENTITY();
    INSERT INTO ADDRESSES VALUES (DEFAULT, temp_id, address);
    SET newid = temp_id;
    END</pre>
<p>In the SQL session, or in the body of another stored procedure, a
      variable must be assigned to the OUT parameter. After the procedure
      call, this variable will hold the new identity value that was generated
      inside the procedure.</p>
<p>In the example below, a session variable,
      <code class="literal">the_new_id</code> is declared. Afther the call to
      <code class="literal">new_customer</code>, the value for the identity is stored in
      <code class="literal">the_new_id</code> variable. This is returned via the next
      CALL statement. Alternatively, <code class="literal">the_new_id</code> can be used
      as an argument to another CALL statement.</p>
<pre class="programlisting">DECLARE the_new_id INT DEFAULT NULL;
CALL new_customer(the_new_id, 'John', 'Smith', '10 Parliament Square'); 
CALL the_new_id;
</pre>
</div>
<div class="section" lang="en">
<div class="titlepage">
<div>
<div>
<h3 class="title">
<a name="N1234C"></a>Iterated Statements</h3>
</div>
</div>
</div>
<p>Various iterated statements can be used in routines. In these
      statements, the <code class="literal">&lt;SQL statement list&gt;</code> consists
      of one or more SQL statements. The <code class="literal">&lt;search
      condition&gt;</code> can be any valid SQL expression of BOOLEAN
      type.</p>
<p>
<code class="literal">&lt;loop statement&gt; ::= [ &lt;beginning label&gt;
      &lt;colon&gt; ] LOOP &lt;SQL statement list&gt; END LOOP [ &lt;ending
      label&gt; ]</code>
</p>
<p>
<code class="literal">&lt;while statement&gt; ::= [ &lt;beginning label&gt;
      &lt;colon&gt; ] WHILE &lt;search condition&gt; DO &lt;SQL statement
      list&gt; END WHILE [ &lt;ending label&gt; ]</code>
</p>
<p>
<code class="literal">&lt;repeat statement&gt; ::= [ &lt;beginning label&gt;
      &lt;colon&gt; ]</code>
</p>
<p>
<code class="literal">REPEAT &lt;SQL statement list&gt; UNTIL &lt;search
      condition&gt; END REPEAT [ &lt;ending label&gt;</code>
</p>
</div>
<div class="section" lang="en">
<div class="titlepage">
<div>
<div>
<h3 class="title">
<a name="N12363"></a>Conditional Statements</h3>
</div>
</div>
</div>
<p>There are two types of CASE ... WHEN statement and the IF ... THEN
      statement.</p>
<a name="N12368" class="indexterm"></a>
<p>
<span class="bold"><strong>CASE WHEN</strong></span>
</p>
<p>
<span class="emphasis"><em>case when statement</em></span>
</p>
<p>The simple case statement uses a <code class="literal">&lt;case
      operand&gt;</code> as the predicand of one or more predicates. For
      the right part of each predicate, it specifies one or more SQL
      statements to execute if the predicate evaluates TRUE. If the ELSE
      clause is not specified, at least one of the search conditions must be
      true, otherwise an exception is raised.</p>
<p>
<code class="literal">&lt;simple case statement&gt; ::= CASE &lt;case
      operand&gt; &lt;simple case statement when clause&gt;... [ &lt;case
      statement else clause&gt; ] END CASE</code>
</p>
<p>
<code class="literal">&lt;simple case statement when clause&gt; ::= WHEN
      &lt;when operand list&gt; THEN &lt;SQL statement
      list&gt;</code>
</p>
<p>
<code class="literal">&lt;case statement else clause&gt; ::= ELSE &lt;SQL
      statement list&gt;</code>
</p>
<p>A skeletal example is given below. The variable var_one is first
      tested for equality with 22 or 23 and if the test evaluates to TRUE,
      then the INSERT statement is performed and the statement ends. If the
      tests does not evaluate to TRUE, the next condition, with is an IN
      predicate is performed with var_one and so on. The statement after the
      ELSE clause is performed if none the previous tests returns TRUE.</p>
<pre class="programlisting">CASE var_one
   WHEN 22, 23 THEN INSERT INTO t_one ...;
   WHEN IN (2, 4, 5) THEN DELETE FROM t_one WHERE ...;
   ELSE UPDATE t_one ...;
   END CASE</pre>
<p>The searched case statement uses one or more search conditions,
      and for each search condition, it specifies one or more SQL statements
      to execute if the search condition evaluates TRUE. An exception is
      raised if there is no ELSE clause and none of the search conditions
      evaluates TRUE.</p>
<p>
<code class="literal">&lt;searched case statement&gt; ::= CASE &lt;searched
      case statement when clause&gt;... [ &lt;case statement else clause&gt; ]
      END CASE</code>
</p>
<p>
<code class="literal">&lt;searched case statement when clause&gt; ::= WHEN
      &lt;search condition&gt; THEN &lt;SQL statement
      list&gt;</code>
</p>
<p>The example below is partly a rewrite of the previous example, but
      a new condition is added:</p>
<pre class="programlisting">CASE var_one = 22 OR var_one = 23 THEN INSERT INTO t_one ...;
   WHEN var_one IN (2, 4, 5) THEN DELETE FROM t_one WHERE ...;
   WHEN var_two IS NULL THEN UPDATE t_one ...;
   ELSE UPDATE t_one ...;
   END CASE</pre>
<a name="N12392" class="indexterm"></a>
<p>
<span class="bold"><strong>IF</strong></span>
</p>
<p>
<span class="emphasis"><em>if statement</em></span>
</p>
<p>The if statement is very similar to the searched case statement.
      The difference is that no exception is raised if there is no ELSE clause
      and no search condition evaluates TRUE.</p>
<p>
<code class="literal">&lt;if statement&gt; ::= IF &lt;search condition&gt;
      &lt;if statement then clause&gt; [ &lt;if statement elseif clause&gt;...
      ] [ &lt;if statement else clause&gt; ] END IF</code>
</p>
<p>
<code class="literal">&lt;if statement then clause&gt; ::= THEN &lt;SQL
      statement list&gt;</code>
</p>
<p>
<code class="literal">&lt;if statement elseif clause&gt; ::= ELSEIF
      &lt;search condition&gt; THEN &lt;SQL statement
      list&gt;</code>
</p>
<p>
<code class="literal">&lt;if statement else clause&gt; ::= ELSE &lt;SQL
      statement list&gt;</code>
</p>
</div>
<div class="section" lang="en">
<div class="titlepage">
<div>
<div>
<h3 class="title">
<a name="N123AC"></a>Return Statement</h3>
</div>
</div>
</div>
<p>The RETURN statement is required and used only in functions. The
      body of a function is either a RETURN statement, or a compound statement
      that contains a RETURN statement.</p>
<a name="N123B1" class="indexterm"></a>
<p>
<span class="bold"><strong>RETURN</strong></span>
</p>
<p>
<span class="emphasis"><em>return statement</em></span>
</p>
<p>
<code class="literal">&lt;return statement&gt; ::= RETURN &lt;return
      value&gt;</code>
</p>
<p>
<code class="literal">&lt;return value&gt; ::= &lt;value expression&gt; |
      NULL</code>
</p>
<p>Return a value from an SQL function. The value expression can
      be any scalar expression. In the examples below, the same function is
      written with or without a BEGIN END block.</p>
<pre class="programlisting">CREATE FUNCTION an_hour_before_max (e_type INT)
RETURNS TIMESTAMP
RETURN (SELECT MAX(event_time) FROM atable WHERE event_type = e_type) - 1 HOUR

CREATE FUNCTION an_hour_before_max (e_type INT)
RETURNS TIMESTAMP
BEGIN ATOMIC
DECLAR max_event TIMESTAMP;
SET max_event = SELECT MAX(event_time) FROM atable WHERE event_type = e_type;
RETURN max_event - 1 HOUR;
END
</pre>
</div>
<div class="section" lang="en">
<div class="titlepage">
<div>
<div>
<h3 class="title">
<a name="N123C7"></a>Control Statements</h3>
</div>
</div>
</div>
<p>In addition to the RETURN statement, the following statements can
      be used in specific contexts.</p>
<p>ITERATE STATEMENT</p>
<p>The ITERATE statement can be used to cause the next iteration of a
      labeled iterated statement (a WHILE, REPEAT or LOOP statement). It is
      similar to the "continue" statement in C and Java.</p>
<p>
<code class="literal">&lt;iterate statement&gt; ::= ITERATE &lt;statement
      label&gt;</code>
</p>
<p>LEAVE STATEMENT</p>
<p>The LEAVE statement can be used to leave a labeled block. When
      used in an iterated statement, it is similar to the "break" statement is
      C and Java. But it can be used in compound statements as well.</p>
<p>
<code class="literal">&lt;leave statement&gt; ::= LEAVE &lt;statement
      label&gt;</code>
</p>
<p>Signal and Resignal Statements</p>
<p>The SIGNAL statement is used to throw an exception (or force an
      excpetion). When invoked, any exception handler for the given exception
      is in turn invoked. If there is no handler, the exception is propagated
      to the enclosing context.</p>
<p>
<code class="literal">&lt;signal statement&gt; ::= SIGNAL SQL_STATE
      &lt;state value&gt;</code>
</p>
<p>The RESIGNAL statement is used to throw an exception from an
      exception handler&rsquo;s <code class="literal">&lt;SQL procedure statement&gt;</code>,
      in effect propagating the exception to the enclosing context without
      further action by the currently active handlers.</p>
<p>
<code class="literal">&lt;resignal statement&gt; ::= RESIGNAL SQL_STATE
      &lt;state value&gt;</code>
</p>
</div>
<div class="section" lang="en">
<div class="titlepage">
<div>
<div>
<h3 class="title">
<a name="N123E9"></a>General SQL Statements</h3>
</div>
</div>
</div>
<p>
<code class="literal">&lt;call statement&gt;</code>
</p>
<p>
<code class="literal">&lt;delete statement&gt;</code>
</p>
<p>
<code class="literal">&lt;insert statement&gt;</code>
</p>
<p>
<code class="literal">&lt;update statement&gt;</code>
</p>
<p>
<code class="literal">&lt;merge statement&gt;</code>
</p>
<p>These statements can be used only in procedures and have the same
      effect as when they are used outside routines. As shown in the examples
      of procedures, the formal parameters and the variables of the procedure
      can be used in these statements.</p>
</div>
<div class="section" lang="en">
<div class="titlepage">
<div>
<div>
<h3 class="title">
<a name="N123FD"></a>Routine Overloading</h3>
</div>
</div>
</div>
<p>More than one version of a routine can be created.</p>
<p>For procedures, the different versions must have different
      parameter counts. &nbsp;When the procedure is called, the parameter count
      determines which version is called.</p>
<p>For functions, the different versions can have the same or
      different parameter counts. When the parameter count of two versions of
      a function is the same, the type of parameters must be different. The
      best matching version of the function is called, according to both the
      parameter count and parameter types.</p>
<p>Two versions of an overloaded function are given below. One
      version accepts TIMESTAMP while the other accepts TIME arguments.</p>
<pre class="programlisting">CREATE FUNCTION an_hour_before_or_now(t TIMESTAMP)
    RETURNS TIMESTAMP
    IF t &gt; CURRENT_TIMESTAMP THEN
    RETURN CURRENT_TIMESTAMP;
    ELSE
    RETURN t - 1 HOUR;
    END IF

CREATE FUNCTION an_hour_before_or_now(t TIME)
    RETURNS TIME
    CASE t
    WHEN &gt; CURRENT_TIME THEN
    RETURN CURRENT_TIME;
    WHEN &gt;= TIME'01:00:00' THEN
    RETURN t - 1 HOUR;
    ELSE
    RETURN CURRENT_TIME;
    END CASE</pre>
<p>more ..</p>
</div>
</div>
<div class="section" lang="en">
<div class="titlepage">
<div>
<div>
<h2 class="title" style="clear: both">
<a name="N1240C"></a>Java Language Routines (SQL/JRT)</h2>
</div>
</div>
</div>
<p>The body of a Java language routine is a static method of a Java
    class, specified with a fully qualified method name in the routine
    definition.</p>
<p>In the example below, the static method named
    <code class="methodname">toZeroPaddedString</code> is specified to be called when
    the function is invoked.</p>
<pre class="programlisting">CREATE FUNCTION zero_pad(x BIGINT, digits INT, maxsize INT)
   RETURNS CHAR VARYING(100)
   NO SQL
   LANGUAGE JAVA PARAMETER STYLE JAVA
   EXTERNAL NAME
   'CLASSPATH:org.hsqldb.lib.StringUtil.toZeroPaddedString'</pre>
<p>The signature of the Java method (used in the Java code but not in
    SQL code to create the function) is given below:</p>
<pre class="programlisting">public static String toZeroPaddedString(long value, int precision, int maxSize)</pre>
<p>The parameter and return types and of the SQL routine definition
    must match those of the Java method according to the table below:</p>
<div class="informaltable">
<table cellspacing="0" style="border-collapse: collapse;border-top: 0.5pt solid ; border-bottom: 0.5pt solid ; border-left: 0.5pt solid ; border-right: 0.5pt solid ; ">
<colgroup>
<col>
<col>
</colgroup>
<tbody>
<tr>
<td style="border-right: 0.5pt solid ; border-bottom: 0.5pt solid ; ">
<p>SMALLINT &nbsp; </p>
</td><td style="border-bottom: 0.5pt solid ; ">
<p>Short or Short</p>
</td>
</tr>
<tr>
<td style="border-right: 0.5pt solid ; border-bottom: 0.5pt solid ; ">
<p>INT</p>
</td><td style="border-bottom: 0.5pt solid ; ">
<p>int or Integer</p>
</td>
</tr>
<tr>
<td style="border-right: 0.5pt solid ; border-bottom: 0.5pt solid ; ">
<p>BIGINT</p>
</td><td style="border-bottom: 0.5pt solid ; ">
<p>long or Long</p>
</td>
</tr>
<tr>
<td style="border-right: 0.5pt solid ; border-bottom: 0.5pt solid ; ">
<p>NUMERIC &nbsp;or DECIMAL</p>
</td><td style="border-bottom: 0.5pt solid ; ">
<p>BigDecimal</p>
</td>
</tr>
<tr>
<td style="border-right: 0.5pt solid ; border-bottom: 0.5pt solid ; ">
<p>FLOAT &nbsp;or DOUBLE</p>
</td><td style="border-bottom: 0.5pt solid ; ">
<p>Double or Double</p>
</td>
</tr>
<tr>
<td style="border-right: 0.5pt solid ; border-bottom: 0.5pt solid ; ">
<p>CHAR or VARCHAR</p>
</td><td style="border-bottom: 0.5pt solid ; ">
<p>String</p>
</td>
</tr>
<tr>
<td style="border-right: 0.5pt solid ; border-bottom: 0.5pt solid ; ">
<p>DATE</p>
</td><td style="border-bottom: 0.5pt solid ; ">
<p>java.sql.Date</p>
</td>
</tr>
<tr>
<td style="border-right: 0.5pt solid ; border-bottom: 0.5pt solid ; ">
<p>TIME</p>
</td><td style="border-bottom: 0.5pt solid ; ">
<p>java.sql.Time</p>
</td>
</tr>
<tr>
<td style="border-right: 0.5pt solid ; border-bottom: 0.5pt solid ; ">
<p>TIMESTAMP</p>
</td><td style="border-bottom: 0.5pt solid ; ">
<p>java.sql.Timestamp</p>
</td>
</tr>
<tr>
<td style="border-right: 0.5pt solid ; border-bottom: 0.5pt solid ; ">
<p>BINARY</p>
</td><td style="border-bottom: 0.5pt solid ; ">
<p>Byte[]</p>
</td>
</tr>
<tr>
<td style="border-right: 0.5pt solid ; border-bottom: 0.5pt solid ; ">
<p>BOOLEAN</p>
</td><td style="border-bottom: 0.5pt solid ; ">
<p>boolean or Boolean</p>
</td>
</tr>
<tr>
<td style="border-right: 0.5pt solid ; ">
<p>TABLE</p>
</td><td style="">
<p>java.sql.ResultSet</p>
</td>
</tr>
</tbody>
</table>
</div>
<p>If the specified Java method is not found or its parameters and
    return types do not match the definition, an exception is raised. If more
    than one version of the Java method exist, then the one with matching
    parameter and return types is found and registered. If two &ldquo;equivalent&rdquo;
    methods exist, the first one is registered. (This situation arises only
    when a parameter is a primitive in one version and an Object in another
    version, e.g. <code class="classname">long</code> and
    <code class="classname">java.lang.Long</code>.).</p>
<p>When the Java method of an SQL/JRT routine returns a value, it
    should be within the size and precision limits defined in the return type
    of the SQL-invoked routine, otherwise an exception is raised. The scale
    difference are ignored and corrected. For example, in the above example,
    the <code class="literal">RETURNS CHAR VARYING(100)</code> clause limits the length
    of the strings returned from the Java method to 100. But if the number of
    digits after the decimal point (scale) of a returned BigDecimal value is
    larger than the scale specified in the RETURNS clause, the decimal
    fraction is silently truncated and no exception of warning is
    raised.</p>
<p>Overloading</p>
<p>If two versions of the same SQL invoked routine with different
    parameter types are required, they can be defined to point to the same
    method name or different method names, or even methods in different
    classes. In the example below, both definitions refer to the same method
    name in the same class. In the Java class, the two static methods are
    defined with corresponding method signatures:</p>
<pre class="programlisting">CREATE FUNCTION an_hour_before_or_now(t TIME)
    RETURNS TIME
    NO SQL
    LANGUAGE JAVA PARAMETER STYLE JAVA
    EXTERNAL NAME 'CLASSPATH:org.npo.lib.nowLessAnHour'

CREATE FUNCTION an_hour_before_or_now(t TIMESTAMP)
    RETURNS TIMESTAMP
    NO SQL
    LANGUAGE JAVA PARAMETER STYLE JAVA
    EXTERNAL NAME 'CLASSPATH:org.npo.lib.nowLessAnHour'</pre>
<p>In the Java class:</p>
<pre class="programlisting">    public static java.sql.Time nowLessAnHour(java.sql.Time value)
    public static java.sql.Timestamp nowLessAnHour(java.sql.Timestamp value)</pre>
<p>Legacy Support</p>
<p>The legacy HyperSQL statement, <code class="literal">CREATE ALIAS &lt;name&gt;
    FOR &lt;fully qualified java method name&gt;</code> is no longer
    supported directly. It is supported when importing databases and
    translates to a special <code class="literal">CREATE FUNCTION &lt;name&gt;</code>
    statement that creates the function in the PUBLIC schema.</p>
<p>The direct use of a Java method as a function is still supported but
    deprecated. It is internally translated to a special <code class="literal">CREATE
    FUNCTION</code> statement where the name of the function is the double
    quoted, fully qualified name of the Java method used.</p>
</div>
<div class="section" lang="en">
<div class="titlepage">
<div>
<div>
<h2 class="title" style="clear: both">
<a name="N1249D"></a>Routine Definition</h2>
</div>
</div>
</div>
<p>As discussed in the previous pages, routine definition has several
    mandatory or optional clauses. The complete BNF supported by HyperSQL and
    the remaining clauses are documented in this section.</p>
<a name="N124A2" class="indexterm"></a>
<p>
<span class="bold"><strong>CREATE FUNCTION</strong></span>
</p>
<a name="N124AB" class="indexterm"></a>
<p>
<span class="bold"><strong>CREATE PROCEDURE</strong></span>
</p>
<p>
<span class="emphasis"><em>routine definition</em></span>
</p>
<p>Routine definition is similar for procedures and functions. A
    function definition has the mandatory <code class="literal">&lt;returns
    clause&gt;</code> which is discussed later. The description given so
    far covers the essential elements of the specification with the BNF given
    below.</p>
<p>
<code class="literal">&lt;schema procedure&gt; ::= CREATE PROCEDURE &lt;schema
    qualified routine name&gt; &lt;SQL parameter declaration list&gt;
    &lt;routine characteristics&gt; &lt;routine body&gt;</code>
</p>
<p>
<code class="literal">&lt;schema function&gt; ::= CREATE FUNCTION &lt;schema
    qualified routine name&gt; &lt;SQL parameter declaration list&gt;
    &lt;returns clause&gt; &lt;routine characteristics&gt; &lt;routine
    body&gt;</code>
</p>
<p>Parameter declaration list has been described above. For SQL/JRT
    routines, the <code class="literal">&lt;SQL parameter name&gt;</code> is optional
    while for SQL/PSM routines, it is required. If the <code class="literal">&lt;parameter
    mode&gt;</code> of a parameter is OUT or INOUT, it must be specified.
    The BNF is given below:</p>
<p>
<code class="literal">&lt;SQL parameter declaration list&gt; ::= &lt;left
    paren&gt; [ &lt;SQL parameter declaration&gt; [ { &lt;comma&gt; &lt;SQL
    parameter declaration&gt; }... ] ] &lt;right paren&gt;</code>
</p>
<p>
<code class="literal">&lt;SQL parameter declaration&gt; ::= [ &lt;parameter
    mode&gt; ] [ &lt;SQL parameter name&gt; ] &lt;parameter
    type&gt;</code>
</p>
<p>
<code class="literal">&lt;parameter mode&gt; ::= IN | OUT |
    INOUT</code>
</p>
<p>
<code class="literal">&lt;parameter type&gt; ::= &lt;data
    type&gt;</code>
</p>
<p>Return Value and Table Functions</p>
<a name="N124D8" class="indexterm"></a>
<p>
<span class="bold"><strong>RETURNS</strong></span>
</p>
<p>
<span class="emphasis"><em>returns clause</em></span>
</p>
<p>The <code class="literal">&lt;returns clause&gt;</code> specifies the type of
    the return value of a function. For all SQL/PSM functions and ordinary
    SQL/JRT functions, this is simply a type definition which can be a
    built-in type, a DOMAIN type or a DISTINCT type. For example, RETURNS
    INTEGER.</p>
<p>(feature to be implemented) For a SQL/JRT function, it is possible
    to define a <code class="literal">&lt;returns table type&gt;</code> for a Java
    method that returns a <code class="classname">java.sql.ResultSet</code> object.
    Such SQL/JRT functions are called <em class="glossterm">table functions</em>.
    Table functions are used differently from normal functions. A table
    function can be used in an SQL query expression exactly where a normal
    table or view is allowed. At the time of invocation, the Java method is
    called and the returned ResultSet is transformed into an SQL table. The
    column types of the declared TABLE must match those of the ResultSet,
    otherwise an exception is raised at the time of invocation.</p>
<p>
<code class="literal">&lt;returns clause&gt; ::= RETURNS &lt;returns
    type&gt;</code>
</p>
<p>
<code class="literal">&lt;returns type&gt; ::= &lt;returns data type&gt; |
    &lt;returns table type&gt;</code>
</p>
<p>
<code class="literal">&lt;returns table type&gt; ::= TABLE &lt;table function
    column list&gt;</code>
</p>
<p>
<code class="literal">&lt;table function column list&gt; ::= &lt;left
    paren&gt; &lt;table function column list element&gt; [ { &lt;comma&gt;
    &lt;table function column list element&gt; } ... ] &lt;right
    paren&gt;</code>
</p>
<p>
<code class="literal">&lt;table function column list element&gt; ::=
    &lt;column name&gt; &lt;data type&gt;</code>
</p>
<p>
<code class="literal">&lt;returns data type&gt; ::= &lt;data
    type&gt;</code>
</p>
<a name="N12506" class="indexterm"></a>
<p>
<span class="emphasis"><em>routine body</em></span>
</p>
<p>Routine body is either one or more SQL statements or a Java
    reference, as described. The user that defines the routine by issuing the
    CREATE FUNCTION or CREATE SCHEMA command must have the relevant access
    rights to all tables, sequences, routines, etc. that are accessed by the
    routine. If another user is given EXECUTE privilege on the routine, then
    there are two possibilities, depending on the <code class="literal">&lt;rights
    clause&gt;</code>. This clause refers to the access rights that are
    checked when a routine is invoked. The default is <code class="literal">SQL SECURITY
    DEFINER</code>, which means access rights of the definer are used;
    therefore no extra checks are performed when the other user invokes the
    routine. The alternative <code class="literal">SQL SECURITY INVOKER</code> means
    access rights on all the database objects referenced by the routine are
    checked for the invoker. This alternative is not supported by
    HyperSQL.</p>
<p>
<code class="literal">&lt;routine body&gt; ::= &lt;SQL routine spec&gt; |
    &lt;external body reference&gt;</code>
</p>
<p>
<code class="literal">&lt;SQL routine spec&gt; ::= [ &lt;rights clause&gt; ]
    &lt;SQL routine body&gt;</code>
</p>
<p>
<code class="literal">&lt;rights clause&gt; ::= SQL SECURITY INVOKER | SQL
    SECURITY DEFINER</code>
</p>
<a name="N12522" class="indexterm"></a>
<p>
<span class="emphasis"><em>SQL routine body</em></span>
</p>
<p>The routine body of a an SQL routine consists of an
    statement.</p>
<p>
<code class="literal">&lt;SQL routine body&gt; ::= &lt;SQL procedure
    statement&gt;</code>
</p>
<a name="N1252F" class="indexterm"></a>
<p>
<span class="bold"><strong>EXTERNAL NAME</strong></span>
</p>
<p>
<span class="emphasis"><em>external body reference</em></span>
</p>
<p>External name specifies the qualified name of the Java method
    associated with this routine. Early releases of HyperSQL 2.0 only supports
    Java methods within the classpath. The &lt;external Java reference
    string&gt; is a quoted string which starts with CLASSPATH: and is followed
    by the java package, class and method names seperated with dots.</p>
<p>
<code class="literal">&lt;external body reference&gt; ::= EXTERNAL NAME
    &lt;external Java reference string&gt; ]</code>
</p>
<p>
<code class="literal">&lt;external Java reference string&gt; ::= &lt;jar and
    class name&gt; &lt;period&gt; &lt;Java method name&gt; [ &lt;Java
    parameter declaration list&gt; ]</code>
</p>
<p>The &lt;routine characteristics&gt; clause covers several
    sub-clauses</p>
<p>
<code class="literal">&lt;routine characteristics&gt; ::= [ &lt;routine
    characteristic&gt;... ]</code>
</p>
<p>
<code class="literal">&lt;routine characteristic&gt; ::= &lt;language
    clause&gt; | &lt;parameter style clause&gt; | SPECIFIC &lt;specific
    name&gt; | &lt;deterministic characteristic&gt; | &lt;SQL-data access
    indication&gt; | &lt;null-call clause&gt; | &lt;returned result sets
    characteristic&gt; | &lt;savepoint level
    indication&gt;</code>
</p>
<a name="N1254B" class="indexterm"></a>
<p>
<span class="bold"><strong>LANGUAGE</strong></span>
</p>
<p>
<span class="emphasis"><em>language clause</em></span>
</p>
<p>The <code class="literal">&lt;language clause&gt;</code> refers to the
    language in which the routine body is written. It is either SQL or Java.
    The default is SQL, so JAVA must be specified for SQL/JRT routines.</p>
<p>
<code class="literal">&lt;language clause&gt; ::= LANGUAGE &lt;language
    name&gt;</code>
</p>
<p>
<code class="literal">&lt;language name&gt; ::= SQL | JAVA</code>
</p>
<p>The parameter style is not allowed for SQL routines. It is optional
    for Java routines and, in HyperSQL, the only value allowed is JAVA.</p>
<p>
<code class="literal">&lt;parameter style&gt; ::= JAVA</code>
</p>
<a name="N12567" class="indexterm"></a>
<p>
<span class="bold"><strong>SPECIFIC NAME</strong></span>
</p>
<p>
<span class="emphasis"><em>specific name</em></span>
</p>
<p>The SPECIFIC <code class="literal">&lt;specific name&gt;</code> clause is
    optional but the engine will creates an automatic name if it is not
    present. When there are several versions of the same routine, the
    <code class="literal">&lt;specific name&gt;</code> is used in schema manipulation
    statements to drop or alter a specific version. The <code class="literal">&lt;specific
    name&gt;</code> is a user-defined name. It applies to both functions
    and procedures. In the examples below, a specific name is specified for
    each function.</p>
<pre class="programlisting">CREATE FUNCTION an_hour_before_or_now(t TIMESTAMP)
    RETURNS TIMESTAMP
    NO SQL
    LANGUAGE JAVA PARAMETER STYLE JAVA
    SPECIFIC an_hour_before_or_now_with_timestamp
    EXTERNAL NAME 'CLASSPATH:org.npo.lib.nowLessAnHour'

CREATE FUNCTION an_hour_before_max (e_type INT)
RETURNS TIMESTAMP SPECIFIC an_hour_before_max_with_int
RETURN (SELECT MAX(event_time) FROM atable WHERE event_type = e_type) - 1 HOUR

</pre>
<p>Determinism and Data Impact</p>
<p>The <code class="literal">&lt;deterministic characteristic&gt;</code> clause
    indicates that an SQL/JRT routine is deterministic or not. Deterministic
    means the routine does not reference random values, external variables, or
    time of invocation. The default is <code class="literal">NOT
    DETERMINISTIC</code>.</p>
<p>
<code class="literal">&lt;deterministic characteristic&gt; ::= DETERMINISTIC |
    NOT DETERMINISTIC</code>
</p>
<p>The <code class="literal">&lt;SQL-data access indication&gt;</code> &nbsp;clause
    indicates the extent to which a routine interacts with the database or the
    data stored in the database tables (SQL data). &nbsp;NO SQL means no SQL
    command is issued in the routine body and can be used only for SQL/JRT
    functions. <code class="literal">CONTAINS SQL</code> means some SQL commands are
    used, but they do not read or modify the SQL data. <code class="literal">READS SQL
    DATA</code> and <code class="literal">MODIFIES SQL DATA</code> are self
    explanatory.</p>
<p>
<code class="literal">&lt;SQL-data access indication&gt; ::= NO SQL | CONTAINS
    SQL | READS SQL DATA | MODIFIES SQL DATA</code>
</p>
<a name="N1259E" class="indexterm"></a>
<p>
<span class="bold"><strong>NULL INPUT</strong></span>
</p>
<p>
<span class="emphasis"><em>null call clause</em></span>
</p>
<p>Null Arguments</p>
<p>The <code class="literal">&lt;null-call clause&gt;</code> is used only for
    functions. If a function returns NULL when any of the calling arguments is
    null, then by specifying <code class="literal">RETURNS NULL ON NULL INPUT</code>,
    calls to the function are known to be redundant and do not take place when
    an argument is null. This simplifies the coding of the SQL/JRT Java
    methods and improves performance at the same time.</p>
<p>
<code class="literal">&lt;null-call clause&gt; ::= RETURNS NULL ON NULL INPUT
    | CALLED ON NULL INPUT</code>
</p>
<p>Transaction Impact</p>
<p>The <code class="literal">&lt;savepoint level indication&gt;</code> is used
    only for procedures and refers to the visibility of existing savepoints
    within the body of the procedure. If <code class="literal">NEW SAVEPOINT
    LEVEL</code> is specified, savepoints that have been declared prior to
    calling the procedure become invisible within the body of the procedure.
    HyperSQL&rsquo;s implementation accepts only <code class="literal">NEW SAVEPOINT
    LEVEL</code>, which must be specified.</p>
<p>
<code class="literal">&lt;savepoint level indication&gt; ::= NEW SAVEPOINT
    LEVEL | OLD SAVEPOINT LEVEL</code>
</p>
<p>ResultSets</p>
<p>The <code class="literal">&lt;returned result sets characteristic&gt;</code>
    is used only for SQL/PSM procedures. The maximum number of result sets
    that a procedure may return can be specified with the clause below. The
    default is zero. Details are discussed in the next section.</p>
<p>
<code class="literal">&lt;returned result sets characteristic&gt; ::= DYNAMIC
    RESULT SETS &lt;maximum returned result sets&gt;</code>
</p>
<p>Cursor Operations and Returning Data From Procedures</p>
<p>The OUT or INOUT parameters of a procedure are used to assign simple
    values to dynamic parameters or to variables in the calling context. In
    addition, a SQL/PSM procedure may return result sets to the calling
    context. These result sets are dynamic in the sense that a procedure may
    return a different number of result sets or none at all in different
    invocations.</p>
<p>The SQL Standard uses a mechanism called CURSORS for accessing and
    modifying rows of a result set one by one. This mechanism is absolutely
    necessary when the database is accessed from an external application
    program. The JDBC ResultSet interface allows this method of access from
    Java programs and is supported by HyperSQL.</p>
<p>The SQL Standard uses cursors within the body of a procedure to
    return result sets. It specifies a somewhat complex mechanism to allow
    access to these cursors from the calling contexts. HyperSQL does not
    support access to such result sets within a calling SQL/PSM procedure.
    This is considered redundant as all operations on data can be performed
    with non-cursor SQL statements.</p>
<p>(feature to be implemented) HyperSQL supports returning single or
    multiple result sets from SQL/PSM procedures only via the JDBC
    CallableStatement interface. Cursors are declared and opened within the
    body of the procedure. No further operation is performed on the cursors
    within the procedure. When the execution of the procedure is complete, the
    cursors become available as Java ResultSet objects via the
    CallableStatement instance that called the SQL/PSM procedure.</p>
</div>
</div>
<HR xmlns:xi="http://www.w3.org/2001/XInclude">
<P xmlns:xi="http://www.w3.org/2001/XInclude" class="svnrev">$Revision: 3201 $</P>
<div class="navfooter">
<hr>
<table summary="Navigation footer" width="100%">
<tr>
<td align="left" width="40%"><a accesskey="p" href="ch07.html"><img src="../images/db/prev.gif" alt="Prev"></a>&nbsp;</td><td align="center" width="20%">&nbsp;</td><td align="right" width="40%">&nbsp;<a accesskey="n" href="triggers-chapt.html"><img src="../images/db/next.gif" alt="Next"></a></td>
</tr>
<tr>
<td valign="top" align="left" width="40%">Chapter&nbsp;7.&nbsp;Data Access and Change&nbsp;</td><td align="center" width="20%"><a accesskey="h" href="index.html"><img src="../images/db/home.gif" alt="Home"></a></td><td valign="top" align="right" width="40%">&nbsp;Chapter&nbsp;9.&nbsp;Triggers</td>
</tr>
</table>
</div>
</body>
</html>
